package com.poispace;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;



import com.poispace.pojo.CellModel;


import net.sf.json.JSONObject;

public class PoiToJson {  
      
    
    private Sheet sheet;    //表格类实例  
    Workbook workBook;
    String contents="";
    int  firstEmptyRow;
    int  secondEmptyRow;
 
    List<CellModel> CellList=null;
    //读取excel文件，创建表格实例  
    private void loadExcel(String filePath) {  
     
    	CellList=new ArrayList<CellModel>();
        FileInputStream inStream = null;  
        try {  
            inStream = new FileInputStream(new File(filePath));  
             workBook = WorkbookFactory.create(inStream);  
             
            sheet = workBook.getSheetAt(0);           
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
            try {  
                if(inStream!=null){  
                    inStream.close();  
                }                  
            } catch (IOException e) {                  
                e.printStackTrace();  
            }  
        }  
    }  
    //获取单元格的值  
    private CellModel getCellValue(Cell cell) {
    	CellModel model=new CellModel();
        String cellValue = "";  
        String cellType = "";  
        CellAddress address =null;
        DataFormatter formatter = new DataFormatter();  
        int firstColumn =0 ;  
        int lastColumn = 0 ;  
        int firstRow = 0 ;  
        int lastRow =0;
        int firstColumn1 =0 ;  
        int lastColumn1 = 0 ;  
        int firstRow1 = 0 ;  
        int lastRow1 =0;
        String area="";
        if (cell != null) {  
        	
        	
        	  address=cell.getAddress();
        	int row=cell.getRowIndex();
        	int column=cell.getColumnIndex();
        	int sheetMergeCount = sheet.getNumMergedRegions();
        	 for(int i = 0 ; i < sheetMergeCount ; i++){  
                 CellRangeAddress ca = sheet.getMergedRegion(i);  
                 
                  firstColumn = ca.getFirstColumn();  
                  lastColumn = ca.getLastColumn();  
                  firstRow = ca.getFirstRow();  
                  lastRow = ca.getLastRow();  
                 if(row >= firstRow && row <= lastRow){  
                     if(column >= firstColumn && column <= lastColumn){  
                    	 //area=""+convertToLetter(firstColumn)+(int)(firstRow+1)+"-"+convertToLetter(lastColumn+65)+(int)(lastRow+1);
//                          area=""+(char)(firstColumn+65)+(int)(firstRow+1)+"-"+(char)(lastColumn+65)+(int)(lastRow+1);
                    	 area=""+convertToLetter(firstColumn)+(int)(firstRow+1)+"-"+convertToLetter(lastColumn)+(int)(lastRow+1);
                          firstRow1=firstRow;
                          firstColumn1=firstColumn;
                          lastRow1=lastRow;
                          lastColumn1=lastColumn;
                        
                     }  
                 }  
             }
       
            //判断单元格数据的类型，不同类型调用不同的方法  
            switch (cell.getCellType()) {  
            
                //数值类型  
                case Cell.CELL_TYPE_NUMERIC:  
                    //进一步判断 ，单元格格式是日期格式   
                    if (DateUtil.isCellDateFormatted(cell)) {  
                        cellValue = formatter.formatCellValue(cell);  
                        
                        cellType="日期格式";
                        
                    } else {  
                        //数值  
                    	cellType="数值格式";
                        double value = cell.getNumericCellValue();  
                        int intValue = (int) value;  
                        cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);  
                    }  
                    break;  
                case Cell.CELL_TYPE_STRING: 
                	cellType="文本格式";
                    cellValue = cell.getStringCellValue();  
                    break;  
                case Cell.CELL_TYPE_BOOLEAN: 
                	cellType="布尔格式";
                    cellValue = String.valueOf(cell.getBooleanCellValue());  
                    break;  
                    //判断单元格是公式格式，需要做一种特殊处理来得到相应的值  
                case Cell.CELL_TYPE_FORMULA:{  
                	cellType="公式格式";
                	
                    try{  
                        cellValue = String.valueOf(cell.getNumericCellValue());  
                    }catch(IllegalStateException e){  
//                        cellValue = String.valueOf(cell.getRichStringCellValue());  
                    }  
                    
                      
                }  
                    break;  
                case Cell.CELL_TYPE_BLANK:  
                	cellType="默认格式";
                    cellValue = "null";  
                    break;  
                case Cell.CELL_TYPE_ERROR:
                	cellType="错误格式";
                    cellValue = "null";  
                    break;  
                default:  
                    cellValue = cell.toString().trim();  
                    break;  
            }  
           
        }  
        
        if(address!=null){
        	HSSFCellStyle cellStyle2 =(HSSFCellStyle) cell.getCellStyle();
        	 try {
				model.setFormula(cell.getCellFormula());
			} catch (Exception e) {
				model.setFormula("");
			}
			if(address.getRow()>=firstRow1&&address.getColumn()>=firstColumn1&&address.getRow()<=lastRow1&&address.getColumn()<=lastColumn1){
        	     //合并单元格的分支
            	if(address.getRow()==firstRow1&&address.getColumn()==firstColumn1){
            		model.setCellName(address.toString());
            		model.setContent(cellValue.replace("\n", "").replace("\r", "").replace("\t", ""));
            		model.setFormat(cellType);
            		 
            		model.setArea(area);
            		HSSFCellStyle cellStyle =(HSSFCellStyle) cell.getCellStyle();
            		model.setFont(cellStyle.getFont(workBook).getFontName());
            		model.setFontSize(cellStyle.getFont(workBook).getFontHeightInPoints()+"pt");
            		
            		
            		HSSFColor color =cellStyle.getFillForegroundColorColor();
            		
            		HSSFColor color2 =cellStyle.getFillBackgroundColorColor();
            		
            		model.setBackgroundColor(convertToStardColor(color));
            		model.setForegroundColor(convertToStardColor(color2));
                	 
            	}
            }else{
               //不包含合并单元格的分支
        		model.setCellName(address.toString());
        		model.setContent(cellValue.replace("\n", "").replace("\r", "").replace("\t", ""));
        		model.setFormat(cellType);
        		
        		model.setArea(address+"-"+address);
        		HSSFCellStyle cellStyle =(HSSFCellStyle) cell.getCellStyle();
        		model.setFont(cellStyle.getFont(workBook).getFontName());
        		model.setFontSize(cellStyle.getFont(workBook).getFontHeightInPoints()+"pt");
        		
        		
        		HSSFColor color =cellStyle.getFillForegroundColorColor();
        		
        		HSSFColor color2 =cellStyle.getFillBackgroundColorColor();
        		model.setBackgroundColor(convertToStardColor(color));
        		model.setForegroundColor(convertToStardColor(color2));
                
            }
        	
        	short a=cellStyle2.getAlignment();
    		if(a==HSSFCellStyle.ALIGN_CENTER){
    			 
    			model.setAlignment("居中对齐");
    		}else if(a==HSSFCellStyle.ALIGN_LEFT){
    		 
    			model.setAlignment("居左对齐");
    		}else if(a==HSSFCellStyle.ALIGN_RIGHT){
    			 
    			model.setAlignment("居右对齐");
    		}
    		
    		short b= cellStyle2.getFont(workBook).getBoldweight();
    		if(b==HSSFFont.BOLDWEIGHT_BOLD){
    			 
    			model.setBold("加粗");
    		}else{
    			model.setBold("");
    		}
    		if(cellStyle2.getFont(workBook).getItalic()){
    			model.setItalic( "斜体");
    		}else{
    			model.setItalic("");
    		}if(cellType.equals("公式格式")||cellType.equals("默认格式")){
    			if(cellStyle2.getDataFormat()<5){
        			model.setFormat("数值格式");
        		}
        		else if(cellStyle2.getDataFormat()<9&&cellStyle2.getDataFormat()>4){
        			model.setFormat("货币格式");
        		}
        		else if(cellStyle2.getDataFormat()<11&&cellStyle2.getDataFormat()>8){
        			model.setFormat("百分比格式");
        		}
        		else if(cellStyle2.getDataFormat()==11){
        			model.setFormat("科学记数格式");
        		}
        		else if(cellStyle2.getDataFormat()<14&&cellStyle2.getDataFormat()>11){
        			model.setFormat("分数格式");
        		}
        		else if(cellStyle2.getDataFormat()<23&&cellStyle2.getDataFormat()>13){
        			model.setFormat("日期格式");
        		}else if(null!=model.getContent()){
        			 Pattern pattern = Pattern.compile("([-\\+]?[1-9]([0-9]*)(\\.[0-9]+)?)|(^0$)");
        		        Matcher isNum = pattern.matcher(model.getContent());
        		        if (!isNum.matches()) {
        		        	model.setFormat("数字格式");
        		        }
        		}else{
        			System.err.println(model.getContent());
        		}
    		}
    		
    		
    		if(cell.getCellStyle().getBorderBottom()==0){
    			model.setBottomFrame("");
    		}    			
    		else{
    			model.setBottomFrame("有");
    		}if(cell.getCellStyle().getBorderTop()==0){
    			model.setTopFrame("");
    		}else{
    			model.setTopFrame("有");
    		}
    		if(cell.getCellStyle().getBorderLeft()==0){
    			model.setLeftFrame("");
    		}else{
    			model.setLeftFrame("有");
    		}if(cell.getCellStyle().getBorderRight()==0){
    			model.setRightFrame("");
        	}else{
        		model.setRightFrame("有");
        	}
    		 
        	
        	 
        }
        
        
        return model;  
    }  
    
    
    public static boolean isNumber(String str) {
        for (int i = 0; i < str.length(); i++) {
           
            if (!Character.isDigit(str.charAt(i))) {
                return false;
            }
        }
        return true;
    }
  
    //转换颜色代码
    private String convertToStardColor(HSSFColor hc) {  
        StringBuffer sb = new StringBuffer("");  
        if (hc != null) {  
            int a = HSSFColor.AUTOMATIC.index;  
            int b = hc.getIndex();  
            if (a == b) {  
                return "";  
            }  
            sb.append("#");  
            for (int i = 0; i < hc.getTriplet().length; i++) {  
                String str;  
                String str_tmp = Integer.toHexString(hc.getTriplet()[i]);  
                if (str_tmp != null && str_tmp.length() < 2) {  
                    str = "0" + str_tmp;  
                } else {  
                    str = str_tmp;  
                }  
                sb.append(str);  
            }  
        }  
        return sb.toString();  
    }  
    
    
    //数字转换字母
    private String convertToLetter(int number) {  
        StringBuffer sb = new StringBuffer("");  
      int i= (number+1)%26;
      int letternumber= (number+1-i)/26;
      if(letternumber!=0&&i!=0){
    	  sb.append((char)(letternumber+65));
      }
      if(i==0){
    	  sb.append((char)(90));
      }else{
    	  sb.append((char)(i+64));
      }
        return sb.toString();  
    }
  
  
    //初始化表格中的每一行，并得到每一个单元格的值  
    public  List<CellModel> init(){  
        int rowNum = sheet.getLastRowNum() + 1;  
        for(int i=0;i<rowNum;i++){ 
        	int emptyrow=0;
        	int emptycolumn=0;
            Row row = sheet.getRow(i);  
            for(int j=0;j<row.getLastCellNum();j++){  
                Cell cell = row.getCell(j);  
                //获取单元格的值  
                CellModel everyem = getCellValue(cell);  
               
                if(null==everyem.getContent()||everyem.getContent().trim().isEmpty()){
                	emptycolumn=emptycolumn+1;
                	
                }
                if(everyem.allEmpty()){
                	
 
                }else{
                	CellList.add(everyem);
                	 
                }
                if(emptycolumn==row.getLastCellNum()){
                	
                	emptyrow++;
                }
                
            }  
            if(emptyrow==1){
            	if(secondEmptyRow==0){
            		firstEmptyRow=0;
            		secondEmptyRow=row.getRowNum();
            	}else{
            		firstEmptyRow=secondEmptyRow;
            		secondEmptyRow=row.getRowNum();
            		
            	}
            	if((secondEmptyRow-firstEmptyRow)==1){
            		
            	
                	break;
            	}
            	
            	
            }
        }  
        return CellList ;
    }  
    public static String [] getFileName(String path)
    {
        File file = new File(path);
        String [] fileName = file.list();
        return fileName;
    }
 
    public static void main(String[] args) {  
    	//文件读路径和文件写路径
    	 readexcel("C:\\Users\\10491\\Downloads","C:\\test2");

        
         
    }
	private static void readexcel(String readpath,String writepath) {
		String [] allname= getFileName(readpath);
    	 for (String string : allname) {
			
			if(string.substring(string.lastIndexOf(".")+1).equals("xls")){
				String excelname=string.substring(0, string.lastIndexOf("."));
				
		        PoiToJson poi = new PoiToJson();  
		        poi.loadExcel(readpath+"\\"+excelname+".xls");  
		        FileOutputStream fop = null;
		        File file;
		        ;
		        
		        Map<String,Object> map = new HashMap<String,Object>();   
		        map.put("CellList",poi.init()); 
		     

		        try {

		         file = new File(writepath+"\\"+excelname+".txt");
		         if(!file.getParentFile().exists()) {
		             //如果目标文件所在的目录不存在，则创建父目录
		             System.out.println("目标文件所在目录不存在，准备创建它！");
		             if(!file.getParentFile().mkdirs()) {
		                 System.out.println("创建目标文件所在目录失败！");
		                 
		             }
		         }
		         fop = new FileOutputStream(file);
		         
		         // if file doesnt exists, then create it
		         if (!file.exists()) {
		          file.createNewFile();
		         }
		         JSONObject jsonObjectFromMap = JSONObject. fromObject(map); 
		         // get the content in bytes
		         byte[] contentInBytes = jsonObjectFromMap.toString().getBytes();

		         fop.write(contentInBytes);
		         fop.flush();
		         fop.close();

		         System.out.println("Done");

		        } catch (IOException e) {
		         e.printStackTrace();
		        } finally {
		         try {
		          if (fop != null) {
		           fop.close();
		          }
		         } catch (IOException e) {
		          e.printStackTrace();
		         }
		        }
			}
		}
	}  
      
}  